#!/bin/sh

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi


LOAD_DIM_PROVINCE="
insert overwrite table xm1_dim.dim_province partition (dt='${data_date}')
select * from (
with area as (
    select
        id, areaname, parentid,bd09_lng, bd09_lat
    from xm1_ods.ods_province
    where dt='${data_date}'
),t1 as (
    select
        id,areaname, parentid,bd09_lng, bd09_lat
    from area
    where parentid=0
),t2 as (
    select
        area.id as id,t1.areaname as province_name,area.areaname as city_name,
        area.parentid,
        area.bd09_lng ,
        area.bd09_lat
    from t1 left join area on area.parentid=t1.id
),t3 as (
    select
        area.id as id,province_name,city_name,area.areaname as district_name,
        area.parentid,
        area.bd09_lng ,
        area.bd09_lat
    from t2 left join area on area.parentid=t2.id
)
select
    area.id as id,province_name,city_name,district_name,area.areaname as street_name,
    area.bd09_lng ,
    area.bd09_lat,
    cottt(area.bd09_lat,area.bd09_lng,6) as geohash
    from t3 left join area on area.parentid=t3.id
where area.bd09_lat is not null  and area.bd09_lng is not null);"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${LOAD_DIM_PROVINCE}"